package com.wx.sql;

import java.util.Map;
import org.apache.log4j.Logger;

public class MarketingSql {
	static Logger logger = Logger.getLogger(MarketingSql.class);
	/**
	 * @Description: 获取活动用户信息
	 * @param @param map
	 * @param @return   
	 * @return String  
	 * @throws
	 * @author liuxin
	 * @date 2018年5月17日
	 */
	public static String getUserString(Map<String, Object> map){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT											 ");
		sql.append(" 	id,                                          ");
		sql.append(" 	user_name,                                   ");
		sql.append(" 	user_code,                                   ");
		sql.append(" 	phone,                                       ");
		sql.append(" 	user_source,                                 ");
		sql.append(" 	user_type,                                   ");
		sql.append(" 	open_id                                      ");
		sql.append(" FROM party_user WHERE user_code = '"+map.get("USER_CODE")+"'         ");
		sql.append(" AND user_source = '"+map.get("USER_SOURCE")+"'                           ");
		sql.append(" AND user_type = '"+map.get("USER_TYPE")+"'                          ");
		logger.info(sql.toString());
		return sql.toString();
	}

	/**
	 * @Description: 获取某个活动规则
	 * @param @param map
	 * @param @return   
	 * @return String  
	 * @throws
	 * @author liuxin
	 * @date 2018年5月17日
	 */
	public static String getRuleString(Map<String, Object> map){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT							 ");
		sql.append(" 	id,                          ");
		sql.append(" 	party_id,                    ");
		sql.append(" 	user_source,                 ");
		sql.append(" 	IFNULL(login_num,0) login_num,                   ");
		sql.append(" 	IFNULL(order_num,0) order_num,                  ");
		sql.append(" 	IFNULL(repeat_num ,0) repeat_num                   ");
		sql.append(" FROM                             ");
		sql.append(" 	party_rule                   ");
		sql.append(" WHERE                            ");
		sql.append(" 	party_id = '"+map.get("PARTY_ID")+"'               ");
		sql.append(" 	AND user_source = '"+map.get("USER_SOURCE")+"'        ");
		logger.info(sql.toString());
		return sql.toString();
	}

	/**
	 * @Description: 获取用户资源
	 * @param @param map
	 * @param @return   
	 * @return String  
	 * @throws
	 * @author liuxin
	 * @date 2018年5月17日
	 */
	public static String getLotteryNum(Map<String, Object> map){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT								 ");
		sql.append(" 	uln.id,                          ");
		sql.append(" 	uln.user_id,                     ");
		sql.append(" 	uln.party_id,                    ");
		sql.append(" 	uln.lottery_num,                 ");
		sql.append(" 	uln.create_date,                 ");
		sql.append(" 	uln.update_date                  ");
		sql.append(" FROM                                ");
		sql.append(" 	party_user_lottery_num uln,      ");
		sql.append(" 	party_user u                     ");
		sql.append(" WHERE                               ");
		sql.append(" 	uln.user_id = u.id               ");
		sql.append(" 	AND u.user_code = '"+map.get("USER_CODE")+"'             ");
		sql.append(" 	AND uln.party_id = '"+map.get("PARTY_ID")+"'            ");
		logger.info(sql.toString());
		return sql.toString();
	}

	/**
	 * @Description: 获取用户最后拉取时间
	 * @param @param map
	 * @param @return   
	 * @return String  
	 * @throws
	 * @author liuxin
	 * @date 2018年5月17日
	 */
	public static String getUserSerInfo(Map<String, Object> map){
		StringBuffer sql = new StringBuffer();

		sql.append(" SELECT      						 ");
		sql.append(" 	us.id,                           ");
		sql.append(" 	us.party_id,                     ");
		sql.append(" 	us.user_id,                      ");
		sql.append(" 	us.last_ser_time,                ");
		sql.append(" 	us.update_date,                  ");
		sql.append(" 	us.create_date                   ");
		sql.append(" FROM                                ");
		sql.append(" 	party_user_serinfo us,           ");
		sql.append(" 	party_user u                     ");
		sql.append(" WHERE                               ");
		sql.append(" 	u.id = us.user_id                ");
		sql.append(" 	AND u.user_code = '"+map.get("USER_CODE")+"'             ");
		sql.append(" 	AND party_id = '"+map.get("PARTY_ID")+"'                ");
		logger.info(sql.toString());
		return sql.toString();
	}

	/**
	 * @Description: 获取奖励组
	 * @param @param map
	 * @param @return   
	 * @return String  
	 * @throws
	 * @author liuxin
	 * @date 2018年5月18日
	 */
	public static String getPrizeGroup(Map<String, Object> map){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT								   ");
		sql.append(" 	ppg.*                              ");
		sql.append(" FROM                                  ");
		sql.append(" 	party_prize_group ppg,             ");
		sql.append(" 	party_info pi                      ");
		sql.append(" WHERE                                 ");
		sql.append(" 	ppg.id = pi.prize_group_id         ");
		sql.append(" 	                                   ");
		sql.append(" 	AND pi.id = '"+map.get("PARTY_ID")+"'                    ");
		logger.info(sql.toString());
		return sql.toString();
	}


	/**
	 * @Description: 根据奖励组合获取奖励明细
	 * @param @param groupId
	 * @param @return   
	 * @return String  
	 * @throws
	 * @author liuxin
	 * @date 2018年5月18日
	 */
	public static String getPrizeDetail (int groupId){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT						");
		sql.append(" 	*                       ");
		sql.append(" FROM                       ");
		sql.append(" 	party_prize_detail d,party_ticket dd       ");
		sql.append(" WHERE d.ticket_id = dd.id and dd.residual_quantity >0 and  ");
		sql.append(" 	group_id = "+groupId+"         ");
		logger.info(sql.toString());
		return sql.toString();
	}

	public static String getTicket (int ticketId){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT						");
		sql.append(" 	*                       ");
		sql.append(" FROM                       ");
		sql.append(" 	party_ticket      ");
		sql.append(" WHERE                      ");
		sql.append(" 	id = "+ticketId+"         ");
		logger.info(sql.toString());
		return sql.toString();
	}

	public static String getPrizeSummary (int prize_id){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT						");
		sql.append(" 	*                       ");
		sql.append(" FROM                       ");
		sql.append(" 	party_prize_summary      ");
		sql.append(" WHERE                      ");
		sql.append(" 	prize_id = "+prize_id+"         ");
		logger.info(sql.toString());
		return sql.toString();
	}

	/**
	 * @Description:获取指定活动的已发个数
	 * @param @param prize_id
	 * @param @return   
	 * @return String  
	 * @throws
	 * @author liuxin
	 * @date 2018年5月18日
	 */
	public static String getTotalNumByPrize (int party_id){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT 								");								
		sql.append(" SUM(IFNULL(prize_num,0)) prize_num,    ");
		sql.append(" prize_id,                              ");
		sql.append(" party_id                               ");
		sql.append(" FROM party_prize_summary               ");
		sql.append(" WHERE party_id = "+party_id+"                 ");
		logger.info(sql.toString());
		return sql.toString();
	}

	//获取抽奖列表
	public static String getAwardDetail (int party_id){
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT								");
		sql.append(" 	ppd.*                           ");
		sql.append(" FROM                               ");
		sql.append(" 	party_prize_detail ppd,         ");
		sql.append(" 	party_prize_group ppg,          ");
		sql.append(" 	party_info pi                   ");
		sql.append(" WHERE                              ");
		sql.append(" ppd.group_id = ppg.id              ");
		sql.append(" AND ppg.id = pi.prize_group_id     ");
		sql.append(" AND pi.id = '"+party_id+"'                    ");
		logger.info(sql.toString());
		return sql.toString();
	}

	public static  String updateTicket(int ticketId) {
		StringBuffer sql = new StringBuffer();
		sql.append(" UPDATE party_ticket 								");
		sql.append(" SET residual_quantity = residual_quantity - 1      ");
		sql.append(" WHERE                                              ");
		sql.append(" 	residual_quantity > 0                           ");
		sql.append(" AND id = "+ticketId+"                              ");
		logger.info(sql.toString());
		return sql.toString();
	}

	public static  String getAwardRecord() {
		StringBuffer sql = new StringBuffer();

		sql.append(" SELECT																				");
		sql.append(" 	ppd.prize_name ,                                                                ");
		sql.append(" 	ppd.remark,                                                                ");
		sql.append(" 	ptd.tmm_ticket_code,                                                            ");
		sql.append(" 	CASE ptd.ticket_status WHEN 1 THEN 0 ELSE 1 END  AS isLost                      ");
		sql.append("                                                                                    ");
		sql.append(" FROM                                                                               ");
		sql.append(" 	party_ticket_detail ptd,                                                        ");
		sql.append(" 	party_ticket pt,                                                                ");
		sql.append(" 	party_info pi,                                                                  ");
		sql.append(" 	party_prize_detail ppd,                                                         ");
		sql.append(" 	party_prize_group ppg                                                           ");
		sql.append(" 	                                                                                ");
		sql.append(" WHERE                                                                              ");
		sql.append("   ptd.ticket_id = pt.id                                                            ");
		sql.append(" 	AND ppd.id = ptd.prize_detail_id                                                       ");
		sql.append(" 	AND ppd.group_id = ppg.id                                                       ");
		sql.append(" 	AND ppg.id = pi.prize_group_id                                                  ");
		sql.append(" 	AND pi.id = ?                                                                 ");
		sql.append(" 	AND ptd.user_id = ?                                                           ");
		sql.append(" 	AND ptd.ticket_status != '0'                                                    ");
		sql.append(" 	ORDER BY ppd.create_date DESC                                                    ");
		logger.info(sql.toString());
		return sql.toString();
	}
}
